package com.minelx.sqlstructures.core.table;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.minelx.sqlstructures.core.ColumnType;
import com.minelx.sqlstructures.core.ResultSetWrapper;
import com.minelx.sqlstructures.core.values.FieldValues;
import com.minelx.sqlstructures.statements.InsertStatement;
import com.minelx.sqlstructures.statements.Query;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import static com.minelx.sqlstructures.core.database.MysqlDatabase.clearSafetyNet;
import static java.lang.String.format;

public class MysqlTable implements ITable {
	private final DataSource dataSource;

	private final String databaseName;

	private final String tableName;

	private final List<FieldValues> fields;

	public MysqlTable(DataSource dataSource, String databaseName, String tableName) {
		this.dataSource = dataSource;
		this.databaseName = databaseName;
		this.tableName = tableName;
		fields = fetchFields(dataSource, databaseName, tableName);
	}

	@Override
	public FieldValues primary() {
		return fields.stream()
				.filter(FieldValues::isPrimary)
				.findFirst().orElseThrow(() -> new RuntimeException("no primary key found in: " + databaseName + "." + tableName));
	}

	@Override
	public void clear() {
		clearSafetyNet(databaseName);
		execute(format("DELETE FROM %s.%s", databaseName, tableName));
	}

	@Override
	public JSONArray toJSON(Query query) {
		String sql = query.statementOf(databaseName + "." + tableName);
		try (Connection connection = dataSource.getConnection();
			 Statement statement = connection.createStatement();
			 ResultSet resultSet = statement.executeQuery(sql)) {
			return ResultSetWrapper.from(resultSet).toJSON();
		} catch (SQLException e) {
			throw new RuntimeException("error while querying to json.", e);
		}
	}

	@Override
	public void attach(JSONArray lines) {
		for (JSONObject line : lines.toJavaList(JSONObject.class)) {
			InsertStatement statement = new InsertStatement(databaseName + "." + tableName);
			for (Map.Entry<String, Object> cellEntry : line.entrySet()) {
				String columnName = cellEntry.getKey();
				String columnValue = cellEntry.getValue().toString();
				String type = fieldOf(columnName).getType();
				statement.put(columnName, ColumnType.from(type).representAsSql(columnValue));
			}
			execute(statement.toSql());
		}
	}

	@Override
	public List<FieldValues> getFields() {
		return fields;
	}

	@Override
	public String getName() {
		return tableName;
	}

	private void execute(String sql) {
		try (Connection connection = dataSource.getConnection();
			 Statement statement = connection.createStatement()) {
			statement.execute(sql);
		} catch (SQLException e) {
			throw new RuntimeException("error while executing: " + sql, e);
		}
	}

	private FieldValues fieldOf(String columnName) {
		return fields.stream()
				.filter(which -> which.getName().equals(columnName))
				.findFirst().orElseThrow(RuntimeException::new);
	}

	private List<FieldValues> fetchFields(DataSource dataSource, String databaseName, String tableName) {
		try (Connection connection = dataSource.getConnection();
			 Statement statement = connection.createStatement()) {
			ResultSet resultSet = statement.executeQuery(
					format("SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT, COLUMN_KEY \n" +
							"FROM information_schema.columns \n" +
							"WHERE TABLE_SCHEMA = '%s' \n" +
							"\tAND TABLE_NAME = '%s' \n;", databaseName, tableName));
			List<FieldValues> result = new ArrayList<>();
			while (resultSet.next()) {
				result.add(new FieldValues(resultSet.getInt("ORDINAL_POSITION"),
						resultSet.getString("COLUMN_NAME"),
						resultSet.getString("COLUMN_TYPE"),
						resultSet.getString("COLUMN_COMMENT"),
						resultSet.getString("COLUMN_KEY").equals("PRI")));
			}
			return result;
		} catch (SQLException e) {
			throw new RuntimeException("error while fetching fields of: " + databaseName + "." + tableName, e);
		}
	}
}
